﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
namespace DA
{
    public class DBRooms : IDisposable
    {
        string sql;
        SqlHelpers sqlh;
        SqlDataReader dr;
        DataSet ds;
        public DBRooms()
        {
            sqlh = new SqlHelpers();
        }
        public DBRooms(SqlHelpers sh)
        {
            sqlh = sh;
        }
        public void Dispose()
        {
            sqlh.Dispose();
        }

        /// <summary>
        /// DA  根据餐台类型  查询所有餐台的方法
        /// </summary>
        /// <param name="rtid">餐台类型编号</param>
        /// <param name="rtname">状态类型编号</param>
        /// <returns></returns>
        public List<CRooms> DBQueryRooms(int rtid, int rtstate)
        {
            List<CRooms> Lcr = new List<CRooms>();
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtid", SqlDbType.Int));
            pars[0].Value = rtid;

            #region 获取餐桌sql
            switch (rtstate)
            {
                case 0:
                    sql = "select * from Rooms where rtId=@rtId and rtState='空闲'";
                    break;
                case 1:
                    sql = "select * from Rooms where rtId=@rtId and rtState='占用'";
                    break;
                case 2:
                    sql = "select * from Rooms where rtId=@rtId and rtState='预定'";
                    break;
                case 3:
                    sql = "select * from Rooms where rtId=@rtId and rtState='脏台'";
                    break;
                case 4:
                    sql = "select * from Rooms where rtId=@rtId and rtState='停用'";
                    break;
                case 5:
                    sql = "select * from Rooms where rtId=@rtId";//某种类型的所有餐桌
                    break;
                default:
                    sql = "select * from Rooms";//所有餐桌
                    break;
            }
            #endregion
            dr = sqlh.RQuery(sql, pars, CommandType.Text);
            while (dr.Read())
            {
                CRooms crooms = new CRooms();
                crooms.CrId1 = Convert.ToInt32(dr["rId"]);
                crooms.CrName1 = dr["rName"].ToString();
                crooms.CrtId1 = Convert.ToInt32(dr["rtId"]);
                crooms.CrtState1 = dr["rtState"].ToString();
                crooms.CobId1 = dr["obId"].ToString();
                crooms.CtrDate1 = dr["rtDate"] is DBNull ? null : (DateTime?)dr["rtDate"];
                crooms.CtrNumber1 = dr["rtNumber"] is DBNull ? null : (int?)dr["rtNumber"];
                crooms.Ceid1 = dr["eid"] is DBNull ? null : (int?)dr["eid"];
                crooms.CtrRemark1 = dr["rtRemark"].ToString();
                Lcr.Add(crooms);
            }
            dr.Close();
            return Lcr;
        }

        /// <summary>
        /// DA 查询餐台数量的方法
        /// </summary>
        /// <param name="tState">自定义状态类型编号</param>
        /// <returns></returns>
        public int DBQueryNumRooms(int rtState)
        {

            if (rtState == 0)
            {
                sql = "select count(*) from Rooms";
            }
            if (rtState == 1)
            {
                sql = "select count(*) from Rooms where rtState='占用'";
            }
            if (rtState == 2)
            {
                sql = "select count(*) from Rooms where rtState='预定'";
            }
            if (rtState == 3)
            {
                sql = "select count(*) from Rooms where rtState='脏台'";
            }
            if (rtState == 4)
            {
                sql = "select count(*) from Rooms where rtState='停用'";
            }
            return sqlh.ExcuteInsert(sql, null);
        }

        /// <summary>
        /// DA 修改餐台状态的方法
        /// </summary>
        /// <param name="rtState">餐台状态</param>
        /// <param name="rtName">名称</param>
        public void DBUpdateRooms(string rtState, string rtName)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtState", SqlDbType.VarChar, 10));
            pars[0].Value = rtState;
            pars.Add(new SqlParameter("@rtName", SqlDbType.VarChar, 10));
            pars[1].Value = rtName;
            sql = "update Rooms set rtState=@rtState where rName=@rtName";
            sqlh.ExcuteInsertUpdateDelete(sql, pars);
        }

        /// <summary>
        /// DA 修改餐台使用状态的方法
        /// </summary>
        /// <param name="cr"></param>
        public void DBRoomsUpdateSY(CRooms cr)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtState", SqlDbType.VarChar, 10));
            pars[0].Value = cr.CrtState1;
            pars.Add(new SqlParameter("@obobId", SqlDbType.VarChar, 20));
            pars[1].Value = cr.CobId1;
            //pars.Add(new SqlParameter("@rtDate", SqlDbType.DateTime));
            //pars[2].Value = cr.CtrDate1  ;
            //pars.Add(new SqlParameter("@rtNumber", SqlDbType.Int));
            //pars[3].Value = cr.CtrNumber1  ;
            //pars.Add(new SqlParameter("@eid", SqlDbType.Int ));
            //pars[4].Value = cr.Ceid1  ;
            pars.Add(new SqlParameter("@rtRemark", SqlDbType.VarChar, 50));
            pars[2].Value = cr.CtrRemark1;
            pars.Add(new SqlParameter("@rtName", SqlDbType.VarChar, 10));
            pars[3].Value = cr.CrName1;
            if (cr.CtrDate1 == null && cr.CtrNumber1 == null && cr.Ceid1 == null)
            {
                sql = "update Rooms set rtState=@rtState,obId=@obobId,rtDate=null,rtNumber=null,eId=null,rtRemark=@rtRemark where rName=@rtName";
            }
            else
            {
                sql = "update Rooms set rtState=@rtState,obId=@obobId,rtDate='" + cr.CtrDate1 + "',rtNumber=" + cr.CtrNumber1 + ",eId=" + cr.Ceid1 + ",rtRemark=@rtRemark where rName=@rtName";
            }

            sqlh.NonQuery(sql, pars, CommandType.Text);

        }

        /// <summary>
        /// DA 查询开单餐台消费总金额的方法
        /// </summary>
        /// <param name="rtzdId"></param>
        /// <returns></returns>
        public string DBRoomsFanHuiMoney(string obzdid)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@zdId", SqlDbType.VarChar, 20));
            pars[0].Value = obzdid;
            pars.Add(new SqlParameter("@sMoney", SqlDbType.VarChar, 10));
            pars[1].Direction = ParameterDirection.Output;
            sql = "pro_getZDMoney";
            sqlh.NonQuery(sql, pars, CommandType.StoredProcedure);
            return pars[1].Value.ToString();
        }

        /// <summary>
        /// DA 查询开单餐台开单时间的方法
        /// </summary>
        /// <param name="tName">餐台名称</param>
        /// <returns></returns>
        public string DBRoomsKaiDangTime(string tName)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtName", SqlDbType.VarChar, 10));
            pars[0].Value = tName;
            pars.Add(new SqlParameter("@comeTime", SqlDbType.DateTime));
            pars[1].Direction = ParameterDirection.Output;
            sql = "pro_getComeTimes";
            sqlh.NonQuery(sql, pars, CommandType.StoredProcedure);
            return pars[1].Value.ToString();
        }

        /// <summary>
        ///DA  根据餐台号查询消费单消费明细信息
        /// </summary>
        /// <param name="rtName"></param>
        /// <returns></returns>
        public DataSet DBRoomsGetKaiDangMingXi(string rtName)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtName", SqlDbType.VarChar, 10));
            pars[0].Value = rtName;
            sql = "select m.msId,m.msName,m.msPrice,b.msAmount,b.msMoney,b.msTime,e.eName,b.msRemark from Menus m inner join BillConsume b on m.msId=b.msId inner join Rooms r on b.obId=r.obId inner join Employees e on e.eId=r.eId where r.rName=@rtName";
            ds = sqlh.ExcuteSelect(sql, "mote", pars);
            return ds;
        }

        /// <summary>
        /// 查询所有占用餐台的方法
        /// </summary>
        /// <returns></returns>
        public List<CRooms> DBRoomsZY()
        {
            List<CRooms> lr = new List<CRooms>();
            sql = "select rName,rtNumber,rtDate,obId,rtRemark from Rooms where rtState='占用'";
            dr = sqlh.RQuery(sql, null, CommandType.Text);
            while (dr.Read())
            {
                CRooms r = new CRooms();
                r.CrName1 = dr["rName"].ToString();
                r.CtrNumber1 = dr["rtNumber"] is DBNull ? null : (int?)dr["rtNumber"];
                r.CtrDate1 = dr["rtDate"] is DBNull ? null : (DateTime?)dr["rtDate"];
                r.CobId1 = dr["obId"].ToString();
                r.CtrRemark1 = dr["rtRemark"].ToString();
                lr.Add(r);
            }
            dr.Close();
            return lr;
        }

        /// <summary>
        /// 获取CRooms实体对象
        /// </summary>
        /// <param name="rId"></param>
        /// <returns></returns>
        public CRooms getEntityById(int rId = 0)
        {
            CRooms r = null;
            sql = "select * from Rooms where rId=" + rId;
            dr = sqlh.RQuery(sql, null, CommandType.Text);
            while (dr.Read())
            {
                int _irId = 0;
                string _rId = dr["rId"] is DBNull ? string.Empty : (!string.IsNullOrEmpty(dr["rId"].ToString()) ? dr["rId"].ToString() : string.Empty);
                int.TryParse(_rId, out _irId);
                if (_irId != 0)
                {
                    r = new CRooms();
                    r.CrId1 = _irId;
                    r.CrName1 = dr["rName"].ToString();
                    r.CrtId1 = dr["rtId"] is DBNull ? 0 : (int)dr["rtId"];
                    r.CrtState1 = dr["rtState"].ToString();
                    r.CobId1 = dr["obId"].ToString();
                    r.CtrDate1 = dr["rtDate"] is DBNull ? null : (DateTime?)dr["rtDate"];
                    r.CtrNumber1 = dr["rtNumber"] is DBNull ? null : (int?)dr["rtNumber"];
                    r.Ceid1 = dr["eId"] is DBNull ? null : (int?)dr["eId"];
                    r.CtrRemark1 = dr["rtRemark"].ToString();
                }
            }
            dr.Close();
            return r;
        }

        /// <summary>
        /// 根据餐厅号模糊查询的方法
        /// </summary>
        /// <param name="tName"></param>
        /// <returns></returns>
        public List<CRooms> DBRoomsMH(string rName)
        {
            List<CRooms> lr = new List<CRooms>();
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rName", SqlDbType.VarChar, 10));
            pars[0].Value = rName;
            sql = "select rName,rtNumber,rtDate,obId,rtRemark from Rooms where rtState='占用' and rName like @rName+'%'";
            dr = sqlh.RQuery(sql, pars, CommandType.Text);
            while (dr.Read())
            {
                CRooms r = new CRooms();
                r.CrName1 = dr["rName"].ToString();
                r.CtrNumber1 = dr["rtNumber"] is DBNull ? null : (int?)dr["rtNumber"];
                r.CtrDate1 = dr["rtDate"] is DBNull ? null : (DateTime?)dr["rtDate"];
                r.CobId1 = dr["obId"].ToString();
                r.CtrRemark1 = dr["rtRemark"].ToString();
                lr.Add(r);
            }
            dr.Close();
            return lr;
        }

        /// <summary>
        /// 查询所有餐台的方法
        /// </summary>
        /// <returns></returns>
        public DataSet DBRoomsSY()
        {
            sql = "select r.rId,r.rName,rt.rtName,r.rtState from Rooms r inner join RoomType rt on r.rtId=rt.rtId";
            ds = sqlh.ExcuteSelect(sql, "TRT", null);
            return ds;
        }

        /// <summary>
        /// 插入餐台的方法
        /// </summary>
        /// <param name="t"></param>
        public void DBRoomsInsert(CRooms r)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rName", SqlDbType.VarChar, 10));
            pars[0].Value = r.CrName1;
            pars.Add(new SqlParameter("@tTypeId", SqlDbType.Int));
            pars[1].Value = r.CrtId1;
            sql = "insert into Rooms values(@rName,@tTypeId,'空闲',null,null,null,null,null)";
            sqlh.ExcuteInsertUpdateDelete(sql, pars);
        }

        /// <summary>
        /// 修改餐台的方法
        /// </summary>
        /// <param name="t"></param>
        public bool DBRoomsUpdate(CRooms r)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rId", SqlDbType.Int));
            pars[0].Value = r.CrId1;
            pars.Add(new SqlParameter("@rName", SqlDbType.VarChar, 10));
            pars[1].Value = r.CrName1;
            pars.Add(new SqlParameter("@rtId", SqlDbType.Int));
            pars[2].Value = r.CrtId1;
            sql = "update Rooms set rName=@rName,rtId=@rtId where rId=@rId";
            return sqlh.ExcuteInsertUpdateDelete(sql, pars);
        }

        public bool upRoom(CRooms r)
        {
            //update [dbo].[Rooms] set rtState='空闲',obId='',rtDate=null,rtNumber=null,eId=null where rId=4 and rtId=1
            //参数化查询 '(@rId int,@rtState varchar(10),@obId varchar(20),@rtDate datetim' 需要参数 '@rtDate'，但未提供该参数。
            //参数化查询 '(@rId int,@rtState varchar(10),@obId varchar(20),@rtDate datetim' 需要参数 '@rtDate'，但未提供该参数。
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rtState", SqlDbType.VarChar, 10));
            pars.Add(new SqlParameter("@obId", SqlDbType.VarChar, 20));
            pars.Add(new SqlParameter("@rtDate", SqlDbType.DateTime));
            pars.Add(new SqlParameter("@rtNumber", SqlDbType.Int));
            pars.Add(new SqlParameter("@eId", SqlDbType.Int));
            pars.Add(new SqlParameter("@rId", SqlDbType.Int));

            pars[0].Value = r.CrtState1;
            pars[1].Value = r.CobId1;
            pars[2].Value = r.CtrDate1 != null ? r.CtrDate1 : DateTime.Now;
            pars[3].Value = r.CtrNumber1 != null ? r.CtrNumber1 : 0;
            pars[4].Value = r.Ceid1 != null ? r.Ceid1 : 0;
            pars[5].Value = r.CrId1;
            sql = "update Rooms set rtState=@rtState,obId=@obId,rtDate=@rtDate,rtNumber=@rtNumber,eId=@eId where rId=@rId";
            return sqlh.ExcuteInsertUpdateDelete(sql, pars);
        }

        /// <summary>
        /// 删除餐台的方法
        /// </summary>
        /// <param name="tId"></param>
        public void DBRoomsDelete(int rId)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rId", SqlDbType.Int));
            pars[0].Value = rId;
            sql = "delete from Rooms where rId=@rId";
            sqlh.ExcuteInsertUpdateDelete(sql, pars);
        }

        /// <summary>
        /// 查询所有餐台名称的方法
        /// </summary>
        /// <returns></returns>
        public List<CRooms> DBRoomsName()
        {
            List<CRooms> lr = new List<CRooms>();
            sql = "select rName from Rooms";
            dr = sqlh.RQuery(sql, null, CommandType.Text);
            while (dr.Read())
            {
                CRooms r = new CRooms();
                r.CrName1 = dr["rName"].ToString();
                lr.Add(r);
            }
            dr.Close();
            return lr;
        }

        /// <summary>
        /// 根据餐台名称返回开单编号的方法
        /// </summary>
        /// <param name="tName"></param>
        /// <returns></returns>
        public string DBRoomsTNameFHID(string rName)
        {
            string zdId = "";
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@rName", SqlDbType.VarChar, 10));
            pars[0].Value = rName;
            sql = "select obId from Rooms where rName=@rName";
            dr = sqlh.RQuery(sql, pars, CommandType.Text);
            if (dr.Read())
            {
                zdId = dr["obId"].ToString();
            }
            dr.Close();
            return zdId;
        }
    }
}
